package controls;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.commons.lang3.StringUtils;

import com.google.gson.Gson;

import util.LogTable;
import util.Name;

import dao.Database;

public class ServletListener implements ServletContextListener{

	private Connection conn = null;
	private Integer pairCount = 0;
	private LogTable logTable = null;
	

	
private ArrayList<String> getCountryList(Connection conn) throws Exception{
		
		String sqlStr = "select distinct loctable.country from cube.loctable";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> cList = new ArrayList<String>();
		
		while(rs.next()){
			String country = rs.getString(1).trim();
			cList.add(country);
		}
		Collections.sort(cList);
		cList.add(0, "none");
		
		return cList;
		 
	}
	
	private ArrayList<String> getCityList(Connection conn,String country) throws Exception{
		String sqlStr = "select distinct loctable.city from cube.loctable where loctable.country='"+country+"'";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		Set <String> set = new HashSet<String>();
		
		while(rs.next()){
			String city = rs.getString(1).trim();
			if(StringUtils.isAlphaSpace(city)){
				set.add(city.toLowerCase());
				//cityList.add(city);
			}
		}
		ArrayList <String> cityList = new ArrayList<String>(set);
		Collections.sort(cityList);
		cityList.add(0, "none");
		return cityList;
	}
	
	private ArrayList<String> getLevel0List(Connection conn) throws Exception{
	
		String sqlStr = "select distinct checkintable.level0Label from cube.checkintable";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> level0List = new ArrayList<String>();
		
		while(rs.next()){
			String level0 = rs.getString(1).trim();
			level0List.add(level0);
		}
		level0List.add(0, "none");
		return level0List;
	}
	
	private ArrayList<String> getLevel1List(Connection conn,String level0) throws Exception{
		String sqlStr = "select distinct checkintable.level1Label from cube.checkintable where checkintable.level0Label='"+level0+"'";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> level1List = new ArrayList<String>();
		
		while(rs.next()){
			String level1 = rs.getString(1).trim();
			level1List.add(level1);
		}
		level1List.remove(new String("empty"));
		level1List.add(0, "none");
		return level1List;
	}
	
	private ArrayList<String> getLevel2List(Connection conn,String level0,String level1) throws Exception{
		String sqlStr = "select distinct checkintable.level2Label from cube.checkintable where checkintable.level0Label='"+level0+"' and checkintable.level1Label='"+level1+"'";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> level2List = new ArrayList<String>();
		
		while(rs.next()){
			String level2 = rs.getString(1).trim();
			level2List.add(level2);
		}
		level2List.remove(new String("empty"));
		level2List.add(0, "none");

		return level2List;
	}
	
	private ArrayList<String> getyearList(Connection conn) throws Exception{
		String sqlStr = "select distinct cube.datetable.year from cube.datetable";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> yearList = new ArrayList<String>();
		
		while(rs.next()){
			String level2 = rs.getString(1).trim();
			yearList.add(level2);
		}
		Collections.sort(yearList);
		yearList.add(0, "none");

		return yearList;
	}
	
	private ArrayList<String> getmonthList(Connection conn,String year) throws Exception{
		String sqlStr = "select distinct cube.datetable.month from cube.datetable where datetable.year='"+year+"'";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> monthList = new ArrayList<String>();
		
		while(rs.next()){
			String level2 = rs.getString(1).trim();
			monthList.add(level2);
		}
		Collections.sort(monthList);
		monthList.add(0, "none");
		
		return monthList;
	}
	
	private ArrayList<String> getdayList(Connection conn,String year,String month) throws Exception{
		String sqlStr = "select distinct cube.datetable.day from cube.datetable where datetable.year='"+year+"' and datetable.month='"+month+"'";
		PreparedStatement stmt = conn.prepareStatement(sqlStr);
		ResultSet rs = stmt.executeQuery();
		ArrayList <String> dayList = new ArrayList<String>();
		
		while(rs.next()){
			String day = rs.getString(1).trim();
			dayList.add(day);
		}
		Collections.sort(dayList);
		dayList.add(0, "none");

		return dayList;
	}	

	
	
	
	
	
	private  void initList(Connection conn) throws Exception{
	
		
		//Level dimension
		ArrayList <String> countryList = getCountryList(conn);
		ArrayList <String> level0List = getLevel0List(conn);
		ArrayList <String> yearList = getyearList(conn);
		
		logTable.countryList = countryList;
		logTable.level0List = level0List;
		logTable.yearList = yearList;
		
		
		
		for(String level0 : level0List){
			String key = Name.level0 + ":"+level0;
			
			ArrayList <String> leve1List = getLevel1List(conn,level0);
			logTable.relationMap.put(key, leve1List);
			
			for(String level1 : leve1List){
				ArrayList <String> level2List = getLevel2List(conn,level0,level1);
				String ikey =key +":"+ Name.level1 +":"+level1;
				logTable.relationMap.put(ikey, level2List);
			}
			
			
		}
		
		
		//Location dimension
		for(String country:countryList){
			String key = Name.country +":"+country;
			ArrayList <String> cityList = getCityList(conn,country);
			logTable.relationMap.put(key, cityList);
		}
		
		
		
		//Time dimension
		for(String year : yearList){
			String key = Name.year + ":" + year;
			ArrayList<String> monthList = getmonthList(conn,year);
			logTable.relationMap.put(key, monthList);
			
			for(String month : monthList){
				String ikey = key+":"+Name.month +":"+month;
				ArrayList <String> dayList = getdayList(conn,year,month);
				logTable.relationMap.put(ikey,dayList);
			}
		}
		
	}
	
	@Override
	public void contextDestroyed(ServletContextEvent contextEvent) {
		// TODO Auto-generated method stub
		System.out.println("End................................");
		try{
			Database db = new Database();
			conn = db.getConnection();
			
			
			ServletContext context =  contextEvent.getServletContext();
			
			logTable = (LogTable)context.getAttribute("logTable");

			Map <String,Map<String,Integer>> pairMap = logTable.pairMap; 
			Map <String,Integer> opcountMap = logTable.opCountMap;
			Integer pairCount = logTable.pairCount;
			Integer opCount = logTable.opCount;
			
			System.out.println("ed:"+pairMap);
			System.out.println("ed:"+opcountMap);
			System.out.println("ed:"+pairCount);
			

			
			String sql = "INSERT INTO log(id,pairmap,opcountmap,paircount,opcount) VALUES (?,?,?,?,?) on duplicate key update pairmap=?,opcountmap=?,paircount=?,opcount=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, 1);
			stmt.setObject(2, pairMap);
			stmt.setObject(3, opcountMap);
			stmt.setInt(4, pairCount);
			stmt.setInt(5, opCount);
			
			stmt.setObject(6, pairMap);
			stmt.setObject(7, opcountMap);
			stmt.setInt(8, pairCount);
			stmt.setInt(9, opCount);
			
			stmt.executeUpdate();
			stmt.close();
			
			
			
			
			
			
			
			conn.close();
			//String url =  context.getRealPath("/log/log.dat");
			
			//ObjectOutputStream out = new ObjectOutputStream(new FileOutputStream(url));
			//out.writeObject(pairMap);
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		
	}

	@Override
	public void contextInitialized(ServletContextEvent contextEvent) {
		// TODO Auto-generated method stub
		
		
		System.out.println("Start..............................");
		
		
		
		ServletContext context =  contextEvent.getServletContext();
		logTable = new LogTable();

	
		Map <String,Map<String,Integer>> pairMap = new HashMap<String,Map<String,Integer>>(); 
		Map <String,Integer> opcount = new HashMap<String,Integer>();
		//pairCount = 0;
		
		Database db = new Database();
		
		System.out.println(context.getRealPath("WEB-INF/log"));
		

		try{
			//String url =  context.getRealPath("log/log.dat");
			//System.out.println(url);
			conn = db.getConnection();
			
			initList(conn);

			
			String sql  = "select pairmap,opcountmap,paircount,opcount from log";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			
			boolean flag = true;
			
			
			while(rs.next()){
				byte [] pairMapBuf = rs.getBytes(1);
				byte [] opBuf = rs.getBytes(2);
				logTable.pairCount = rs.getInt(3);
				logTable.opCount = rs.getInt(4);
				
				ObjectInputStream in = new ObjectInputStream(new ByteArrayInputStream(pairMapBuf));
				logTable.pairMap = (Map<String,Map<String,Integer>>)in.readObject();
				
				ObjectInputStream inOp = new ObjectInputStream(new ByteArrayInputStream(opBuf));
				logTable.opCountMap = (Map<String,Integer>)inOp.readObject();
				
				//flag = false;
				in.close();
				inOp.close();
			}
			
			conn.close();
			
			
		}
		catch(Exception e){
			System.out.print("xxxx");
			e.printStackTrace();
		}
		
		context.setAttribute("logTable", logTable);
	
		System.out.println("st:"+logTable.pairMap);
		System.out.println("st:"+logTable.opCountMap);
		System.out.println("st:"+logTable.pairCount);
		System.out.print("st:"+logTable.opCount);
	
	}

}
